package com.iplant.base.utils.excel;

import com.iplant.base.po.ServiceResult;
import com.iplant.base.utils.excel.ExcelSheetUtil.ExcelStyle;
import com.iplant.base.utils.general.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.util.*;
import java.util.stream.Collectors;

public class ServerExcelUtils {
	public enum ExcelExtType {
		error, xls, xlsx,

	}

	private static final Logger logger = LoggerFactory.getLogger(ServerExcelUtils.class);
	private static ServerExcelUtils Instance;

	public static ServerExcelUtils getInstance() {

		if (Instance == null) {
			Instance = new ServerExcelUtils();
		}
		return Instance;

	}

	private ServerExcelUtils() {
	}

	/**
	* 上传Excel导入
	**/

	public ServiceResult<List<Map<String, Object>>> Import(MultipartFile file, String sheetName) {
		ServiceResult<List<Map<String, Object>>> wResult = new ServiceResult<List<Map<String, Object>>>();
		wResult.Result = new ArrayList<Map<String, Object>>();
		wResult.FaultCode = "";
		try {

			if (file == null || file.getSize() <= 0) {
				wResult.FaultCode = "请选择要导入的Excel文件";
				return wResult;
			}
			ExcelExtType excelType = GetExcelFileType(file.getOriginalFilename());
			if (excelType == ExcelExtType.error) {
				wResult.FaultCode = "请选择正确的Excel文件";
				return wResult;
			}

			wResult.Result = this.ImportExcel(file.getInputStream(), excelType, sheetName);
			if (wResult.Result == null) {
				wResult.FaultCode = "导入失败,请选择正确的Excel文件";
			}

		} catch (Exception e) {
			wResult.FaultCode += e.toString();
			logger.error(e.toString());
		}
		return wResult;
	}

	private ExcelExtType GetExcelFileType(String wFileFullPath) {

		// 2007版本
		if (wFileFullPath.indexOf(".xlsx") > 0) {
			return ExcelExtType.xlsx;
		}
		// 2003版本
		else if (wFileFullPath.indexOf(".xls") > 0) {
			return ExcelExtType.xls;
		}

		return ExcelExtType.error;
	}

	/**
	* 根据Excel格式读取Excel
	**/

	private List<Map<String, Object>> ImportExcel(InputStream stream, ExcelExtType type, String sheetName) {
		List<Map<String, Object>> dt = new ArrayList<Map<String, Object>>();
		Workbook workbook = null;
		try {

			// xls使用HSSFWorkbook类实现，xlsx使用XSSFWorkbook类实现
			switch (type) {
			case xlsx:
				workbook = new XSSFWorkbook(stream);
				break;
			default:
				workbook = new HSSFWorkbook(stream);
				break;
			}
			Sheet sheet = null;
			// 获取工作表 默认取第一张
			if (StringUtils.isEmpty(sheetName)) {
				sheet = workbook.getSheetAt(0);
			} else {
				sheet = workbook.getSheet(sheetName);
			}

			if (sheet == null) {
				return dt;
			}
			// IEnumerator rows = sheet.GETR();
			// sheet.NumMergedRegions

			int wRowIndex = sheet.getFirstRowNum();

			// region 获取表头
			Row headerRow = sheet.getRow(wRowIndex);

			Boolean wIsMergedRegion=false;

			for (int i = sheet.getFirstRowNum(); i < sheet.getLastRowNum(); i++) {
				wIsMergedRegion=false;
				for (int j = headerRow.getFirstCellNum(); j < headerRow.getLastCellNum(); j++) {
					if (this.isMergedRegion(sheet, i, j)) {
						wIsMergedRegion=true;
						break;
					}
				}
				if(!wIsMergedRegion){
					wRowIndex=i+1;
					break;
				}
			}

			Map<Integer, String> wRowStringList = new HashMap<Integer, String>();

			int cellCount = headerRow.getLastCellNum();
			for (int j = 0; j < cellCount; j++) {

				Cell cell = headerRow.getCell(j);
				if (cell != null) {

					wRowStringList.put(j, cell.toString());
				} else {
					wRowStringList.put(j, "");
				}
			}

			// endRegion

			// region 获取内容
			for (int i = wRowIndex; i <= sheet.getLastRowNum(); i++) {
				Row row = sheet.getRow(i);

				Map<String, Object> wRowObject = new HashMap<String, Object>();

				for (int j = row.getFirstCellNum(); j < cellCount; j++) {
					if (row.getCell(j) != null) {
						// 判断单元格是否为日期格式
						switch (row.getCell(j).getCellType()) {
						case BLANK:
							wRowObject.put(wRowStringList.get(j), "");
							break;
						case BOOLEAN:
							wRowObject.put(wRowStringList.get(j), row.getCell(j).getBooleanCellValue());
							break;
						case ERROR:
							wRowObject.put(wRowStringList.get(j), row.getCell(j).toString());
							break;
						case FORMULA:
							wRowObject.put(wRowStringList.get(j), row.getCell(j).toString());
							break;
						case NUMERIC:
							if (DateUtil.isCellDateFormatted(row.getCell(j))) {
								Calendar wTime = Calendar.getInstance();
								wTime.setTime(row.getCell(j).getDateCellValue());
								wRowObject.put(wRowStringList.get(j), wTime);
							} else {
								wRowObject.put(wRowStringList.get(j), row.getCell(j).getNumericCellValue());
							}
							break;
						case STRING:
							wRowObject.put(wRowStringList.get(j), row.getCell(j).getStringCellValue());
							break;
						case _NONE:
							wRowObject.put(wRowStringList.get(j), row.getCell(j).toString());
							break;

						default:
							wRowObject.put(wRowStringList.get(j), row.getCell(j).toString());
							break;
						}

					}
				}
				dt.add(wRowObject);
			}
			// endRegion

		} catch (Exception ex) {

			logger.error(ex.toString());
			dt = null;
		} finally {
			try {
				if (workbook != null) {
					workbook.close();
				}
			} catch (Exception e) {
				logger.error(e.toString());
			}
		}
		return dt;
	}

	public boolean isMergedRegion(Sheet sheet, int row, int column) {

		int sheetMergeCount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetMergeCount; i++) {
			CellRangeAddress ca = sheet.getMergedRegion(i);
			int firstColumn = ca.getFirstColumn();
			int lastColumn = ca.getLastColumn();
			int firstRow = ca.getFirstRow();
			int lastRow = ca.getLastRow();
			if (row >= firstRow && row <= lastRow) {
				if (column >= firstColumn && column <= lastColumn) {
					return true;
				}
			}
		}

		return false;
	}

	public ServiceResult<String> Export(List<Map<String, Object>> wInputList, Map<String, String> wHeadTitle,
			String wTitle, String wFileName, List<String> wOrderList, String wSavePath, String wStaticPath) {

		ServiceResult<String> wResult = new ServiceResult<String>();
		Workbook wIWorkbook = null;
		FileOutputStream wOut = null;
		try {
			wResult.FaultCode = "";
			wResult.Result = "";
			wTitle = StringUtils.isEmpty(wTitle) ? "Sheet1" : wTitle;
			wOrderList = wOrderList == null ? new ArrayList<>(wHeadTitle.keySet()) : wOrderList;
			if (wHeadTitle == null || wHeadTitle.size() == 0) {
				wResult.FaultCode = "导出Excel文件输入数据为空！！";
				return null;
			}
			ExcelExtType excelType = GetExcelFileType(wFileName);
			if (excelType == ExcelExtType.error) {
				wResult.FaultCode = "请选择正确的Excel文件名";
				return null;
			}

			switch (excelType) {

			case xls:
				wIWorkbook = new HSSFWorkbook();
				break;
			case xlsx:
				wIWorkbook = new XSSFWorkbook();
				;
				break;
			default:
				break;
			}
			Map<String, Sheet> wISheetList = new HashMap<String, Sheet>();

			Sheet wISheet = wIWorkbook.createSheet(wTitle);
			wISheetList.put(wTitle, wISheet);

			List<String> wOrderColumnList = wOrderList.stream().map(p -> wHeadTitle.get(p))
					.collect(Collectors.toList());

			Map<ExcelStyle, CellStyle> wICellStyleDictionary = new HashMap<ExcelSheetUtil.ExcelStyle, CellStyle>();

			ExcelSheetUtil.getInstance().SetHeadToSheet(wISheetList.get(wTitle), wTitle, wOrderColumnList, 0,
					wICellStyleDictionary);

			ExcelSheetUtil.getInstance().SetListToSheet(wInputList, wISheetList.get(wTitle), wOrderList, 2, 0,
					wICellStyleDictionary);

			Calendar wNow = Calendar.getInstance();

			if (!wSavePath.startsWith("/")) {
				wSavePath = "/" + wSavePath;
			}

			String wFileFullName = StringUtils.CombinePath(wSavePath,
					StringUtils.parseCalendarToString(wNow, "yyyy/MM/dd/"));

			File wFile = new File(StringUtils.CombinePath(wStaticPath, wFileFullName));
			if (!wFile.exists()) {
				wFile.mkdirs();
			}

			wFileFullName = wFileFullName + wFileName;

			wOut = new FileOutputStream(StringUtils.CombinePath(wStaticPath, wFileFullName));

			wIWorkbook.write(wOut);

			wResult.Result = wFileFullName;
		} catch (Exception e) {
			wResult.FaultCode += e.toString();
			logger.error(e.toString());
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (wIWorkbook != null) {
					wIWorkbook.close();
				}
			} catch (Exception e) {
				logger.error(e.toString());
			}
			try {
				if (wOut != null) {
					wOut.close();
				}
			} catch (Exception e) {
				logger.error(e.toString());
			}
		}
		return wResult;
	}

}